package jdbc.homework02;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jdbc.homework01.DbUtil;

public class StudentDao {

	// 查询所有学生信息
	public static List<Student> findAll() {
		List<Student> list = null;
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from s_student";
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			list = new ArrayList<Student>();
			while (rs.next()) {
				long stuno = rs.getLong(1);
				String sname = rs.getString(2);
				String sex = rs.getString(3);
				String tel = rs.getString(4);
				String qq = rs.getString(5);
				String wechat = rs.getString(6);
				long gradeid = rs.getLong(7);
				Date enterdate = rs.getDate(8);
				String fromcity = rs.getString(9);
				Date brithday = rs.getDate(10);
				String address = rs.getString(11);
				String pass = rs.getString(12);
				Student st = new Student(stuno, sname, sex, tel, qq, wechat, gradeid, enterdate, fromcity, brithday,
						address, pass);

				list.add(st);
			}
			return list;
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			DbUtil.close(rs, null, ps, con);
		}

		return null;
	}

	// 增加新学生
	public static int addStudent(Student st) {
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "insert into s_student(sname,sex,telephone,qq,wechat,gradeid,enterdate,fromcity,birthday,address,pass) values(?,?,?,?,?,?,?,?,?,?,?)";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, st.getSname());
			ps.setString(2, st.getSex());
			ps.setString(3, st.getTel());
			ps.setString(4, st.getQq());
			ps.setString(5, st.getWechat());
			ps.setLong(6, st.getGradeid());
			ps.setDate(7, st.getEnterdate());
			ps.setString(8, st.getFromcity());
			ps.setDate(9, st.getBrithday());
			ps.setString(10, st.getAddress());
			ps.setString(11, st.getPass());

			int r = ps.executeUpdate();
			return r;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtil.close(rs, null, ps, con);
		}

		return 0;
	}

	// 修改学生信息
	public static int update(Student st) {
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "update s_student set sname=?,sex=? where stuno=?";

		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, st.getSname());
			ps.setString(2, st.getSex());
			ps.setLong(3, st.getStuno());

			int r = ps.executeUpdate();
			return r;
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return 0;
	}

	// 删除学生
	public static int delete(Student st) {
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "delete from s_student  where stuno=?";
		try {
			ps = con.prepareStatement(sql);
			ps.setLong(1, st.getStuno());
			int r = ps.executeUpdate();
			return r;
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DbUtil.close(rs, null, ps, con);
		}

		return 0;
	}

	// 分页查询

	// pageNo 第几页
	// pagesize 一页多少条
	public static List<Student> limiteSelect(int pageNo, int pagesize) {
		List<Student> list = null;
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from s_student limit ?,?";
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, (pageNo - 1) * pagesize);
			ps.setInt(2, pagesize);
			rs = ps.executeQuery();
			list = new ArrayList<Student>();
			while (rs.next()) {
				long stuno = rs.getLong(1);
				String sname = rs.getString(2);
				String sex = rs.getString(3);
				String tel = rs.getString(4);
				String qq = rs.getString(5);
				String wechat = rs.getString(6);
				long gradeid = rs.getLong(7);
				Date enterdate = rs.getDate(8);
				String fromcity = rs.getString(9);
				Date brithday = rs.getDate(10);
				String address = rs.getString(11);
				String pass = rs.getString(12);
				Student st = new Student(stuno, sname, sex, tel, qq, wechat, gradeid, enterdate, fromcity, brithday,
						address, pass);

				list.add(st);
			}
			return list;
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			DbUtil.close(rs, null, ps, con);
		}

		return null;
	}

	// 分组查询
	public static Map<String, String> groupBy() {
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select sex , count(*) as num from s_student group by sex";
		Map<String, String> map = new HashMap<String, String>();

		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				// System.out.println(rs.getString("sex") + " " + rs.getInt("num"));
				map.put(rs.getString("sex"), rs.getInt("num") + "");

			}
			return map;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtil.close(rs, null, ps, con);
		}
		return null;
	}

	// 统计人数
	public static int count() {
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int num = 0;
		String sql = "select count(*) from s_student ";
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				num = rs.getInt(1);
			}
			return num;
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return num;
	}

	// 查询

	// 查询：根据姓名 和手机号，以及地址做模糊查询
	// 有可能条件没有提供，如果不提供，忽略这个条件
	public List<Student> paging(String name, String phone, String addr, Long gradeid, String qq) {
		String sql = "select * from s_student where 1=1 ";
		// 放参数
		List<Object> params = new ArrayList<>(5);
		if (name != null && !"".equals(name)) {
			sql += " and sname like   ? ";
			params.add("%" + name + "%");

		}
		if (phone != null && !"".equals(phone)) {
			sql += " and telephone like   ? ";
			params.add("%" + phone + "%");
		}
		if (addr != null && !"".equals(addr)) {
			sql += " and address like   ? ";
			params.add("%" + addr + "%");
		}
		if (gradeid != null && gradeid > 0) {
			sql += " and gradeid = ?";
			params.add(gradeid);
		}
		if (qq != null && !"".equals(qq)) {
			sql += " and qq like ?";
			params.add("%" + qq + "%");
		}
		System.out.println(sql + ">>." + params);
		List<Student> list = new ArrayList<>();
		Connection con = DbUtil.getConnection();
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			st = con.prepareStatement(sql);
			for (int i = 0; i < params.size(); i++) {
				st.setObject(i + 1, params.get(i));
			}

			rs = st.executeQuery();
			while (rs.next()) {
				Student s = new Student();
				s.setStuno(rs.getInt("stuno"));
				s.setSname(rs.getString("sname"));
				s.setSex(rs.getString("sex"));
				s.setTel(rs.getString("telephone"));
				s.setQq(rs.getString("qq"));
				s.setWechat(rs.getString("wechat"));
				s.setGradeid(rs.getInt("gradeid"));
				s.setEnterdate(rs.getDate("enterdate"));
				s.setFromcity(rs.getString("fromcity"));
				s.setBrithday(rs.getDate("birthday"));
				s.setAddress(rs.getString("address"));
				s.setPass(rs.getString("pass"));

				list.add(s);
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DbUtil.close(rs, null, st, con);
		}
		return list;
	}

//	添加一个查询特点条件的学生总数的方法  public int  total(String name, String phone, String addr, Long gradeid, Date enterDateStart,String qq) 
	public static int count(String name, String phone, String addr, Long gradeid, Date enterDate, String qq) {
		String sql = "select count(*) from s_student where 1=1  ";
		List<Object> sqlList = new ArrayList<Object>();
		if (name != null && !"".equals(name)) {
			sql += " and sname like ?";
			sqlList.add("%" + name + "%");
		}
		if (phone != null && !"".equals(phone)) {
			sql += " and telephone like ?";
			sqlList.add("%" + phone + "%");
		}
		if (addr != null && !"".equals(addr)) {
			sql += " and address like ?";
			sqlList.add("%" + addr + "%");
		}
		if (gradeid != null && gradeid > 0) {
			sql += " and gradeid = ?";
			sqlList.add(gradeid);
		}
		if (enterDate != null) {
			sql += " and enterdate >? ";
			sqlList.add("%" + enterDate + "%");
		}
		if (qq != null && !"".equals(qq)) {
			sql += " and qq like ?";
			sqlList.add("%" + qq + "%");
		}
		System.out.println(sql + ">>." + sqlList);
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int r = 0;
		try {
			ps = con.prepareStatement(sql);
			for (int i = 0; i < sqlList.size(); i++) {
				ps.setObject(i + 1, sqlList.get(i));
			}
			rs = ps.executeQuery();
			if (rs.next()) {
				r = rs.getInt(1);
			}

			return r;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtil.close(rs, null, ps, con);
		}

		return r;
	}

}
